﻿//Copyright (c) 2010 Christopher E. S. King (http://chriskingconsulting.com)
//
//Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files 
// (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, 
// publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do 
// so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF 
// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE 
// FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq.Mapping;
using System.Data.Linq;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections;
using System.Text.RegularExpressions;
using System.Reflection;

using King.Extensions;
using King.Reflection;

// http://msdn.microsoft.com/en-us/library/bb386947.aspx
// http://msdn.microsoft.com/en-us/library/ms187752.aspx

namespace King.Database.Microsoft {

    public static class Extensions {
        internal static string[] AllSqlTypes;
        internal static string[] GetSqlTypes(this Type type) {
            if (type == typeof(SqlObject)) {
                if (AllSqlTypes == null) {
                    var allSqlTypes =
                        from o in Assembly.GetExecutingAssembly().GetTypes()
                        where o.IsSubclassOf(typeof(SqlObject))
                        from x in o.GetSqlTypes()
                        select x;
                    AllSqlTypes = allSqlTypes.Distinct().ToArray();
                }

                return AllSqlTypes;
            }
            return type.GetAttributes<SqlObjectAttribute>().SelectMany(x => x.Types).ToArray();
        }

        public static IEnumerable<SqlObject> GetObjects(this ISqlScope scope) {
            return scope.Get<SqlObject>();
        }
        public static IEnumerable<SqlTable> GetTables(this ISqlScope scope) {
            return scope.Get<SqlTable>();
        }
        public static IEnumerable<SqlSynonym> GetSynonyms(this ISqlScope scope) {
            return scope.Get<SqlSynonym>();
        }
        public static IEnumerable<SqlForeignKey> GetForeignKeys(this ISqlScope scope) {
            return scope.Get<SqlForeignKey>();
        }
        public static IEnumerable<SqlKeyConstraint> GetKeyConstraints(this ISqlScope scope) {
            return scope.Get<SqlKeyConstraint>();
        }

        public static SqlObject GetObject(this ISqlScope scope, string name) {
            var objects = scope.Get<SqlObject>(name);
            return objects.SingleOrDefault();
        }
        public static SqlSynonym GetSynonym(this ISqlScope scope, string name) {
            var objects = scope.Get<SqlSynonym>(name);
            return objects.SingleOrDefault();
        }
        public static SqlTable GetTable(this ISqlScope scope, string name) {
            var objects = scope.Get<SqlTable>(name);
            return objects.SingleOrDefault();
        }
        public static SqlForeignKey GetForeignKey(this ISqlScope scope, string name) {
            var objects = scope.Get<SqlForeignKey>(name);
            return objects.SingleOrDefault();
        }
        public static SqlKeyConstraint GetKeyConstraints(this ISqlScope scope, string name) {
            var objects = scope.Get<SqlKeyConstraint>(name);
            return objects.SingleOrDefault();
        }
    }

    #region SqlAttributes           (SqlTypeAttribute)
    public class SqlTypeAttribute : Attribute {

        #region Constructor
        public SqlTypeAttribute() { }
        public SqlTypeAttribute(SqlTypeType sqlTypeType) {
        }
        #endregion

        #region Public Named Members
        public long Length { get; set; }
        public long Precision { get; set; }
        public long Scale { get; set; }
        #endregion

        #region Public Members
        public bool IsMax { get { return Length == long.MaxValue; } }
        #endregion
    }
    public class SqlForeignKeyAttribute : Attribute {

        #region Private Data Members
        private Type m_targetTable;
        #endregion

        #region Constructor
        public SqlForeignKeyAttribute(Type targetTable) {
            m_targetTable = targetTable;
        }
        #endregion

        #region Public Members
        public string Pluralization { get; set; }
        #endregion
    }
    [AttributeUsage(AttributeTargets.Field | AttributeTargets.Property, AllowMultiple = true)]
    public class SqlIndexAttribute : Attribute {

        #region Private Data Members
        private string m_name;
        private bool m_isUnique;
        private IndexType m_indexType;
        #endregion

        #region Constructor
        public SqlIndexAttribute(string name, IndexType indexType) {
            m_name = name;
            m_indexType = indexType;
        }
        #endregion

        #region Public Members
        public string Name { get { return m_name; } }
        public IndexType IndexType { get { return m_indexType; } }
        #endregion

        #region Public Named Members
        public bool IsPrimaryKey { get; set; }
        public bool IsUnique {
            get { return IsPrimaryKey || m_isUnique; }
            set { m_isUnique = value; }
        }
        #endregion
    }
    #endregion

    #region SqlType                 (SqlType, SqlBinaryType, SqlTypeType, SqlTypeInfoAttribute)
    [Flags]
    public enum SqlBinaryType {
        Number = 1 << 0,
        Boolean = Number | 1 << 1,
        Unsigned = Number | 1 << 2,
        Signed = Number | 1 << 3,
        Decimal = Number | 1 << 4,
        Monetary = Number | 1 << 5,
        Approximate = Number | 1 << 6,

        Character = 1 << 7,
        Unicode = Character | 1 << 8,

        DateTime = 1 << 9,

        Binary = 1 << 10,

        Guid = 1 << 11,

        Object = 1 << 12,
    }
    public sealed class SqlTypeInfoAttribute : Attribute {

        #region Private Data Members
        private string m_sqlName;
        private Type m_type;
        private SqlBinaryType m_group;
        #endregion

        #region Constructor
        public SqlTypeInfoAttribute(string sqlName, Type type, SqlBinaryType group) {
            m_sqlName = sqlName;
            m_group = group;
            m_type = type;
        }
        #endregion

        #region Public Members
        public Type Type { get { return m_type; } }
        public string Name { get { return m_sqlName; } }
        #endregion

        #region NamedMembers
        public int MaxBytes { get; set; }
        public int MaxDigits { get; set; }
        public int MaxDecimalDigits { get; set; }
        public bool IsNullable { get; set; }
        public bool IsVariableType { get; set; }
        public bool IsPrecisionScaleType { get; set; }
        #endregion
    }
    public enum SqlTypeType {

        #region Boolean
        [SqlTypeInfo("bit", typeof(bool), SqlBinaryType.Boolean, MaxBytes = 1)]
        Bit,
        #endregion

        #region Unsigned, Signed
        [SqlTypeInfo("tinyint", typeof(byte), SqlBinaryType.Unsigned, MaxBytes = 1)]
        TinyInt,
        [SqlTypeInfo("smallint", typeof(short), SqlBinaryType.Signed, MaxBytes = 2)]
        SmallInt,
        [SqlTypeInfo("int", typeof(int), SqlBinaryType.Signed, MaxBytes = 4)]
        Int,
        [SqlTypeInfo("bigint", typeof(long), SqlBinaryType.Signed, MaxBytes = 8)]
        BigInt,
        #endregion

        #region SmallMoney
        [SqlTypeInfo("smallmoney", typeof(int), SqlBinaryType.Monetary, MaxBytes = 4, MaxDigits = 10, MaxDecimalDigits = 4)]
        SmallMoney,
        [SqlTypeInfo("money", typeof(long), SqlBinaryType.Monetary, MaxBytes = 8, MaxDigits = 19, MaxDecimalDigits = 4)]
        Money,
        #endregion

        #region Approimate
        [SqlTypeInfo("real", typeof(float), SqlBinaryType.Approximate, MaxBytes = 4)]
        Real,
        [SqlTypeInfo("float", typeof(double), SqlBinaryType.Approximate, MaxBytes = 8)]
        Float,
        #endregion

        #region Decimal
        [SqlTypeInfo("decimal", typeof(decimal), SqlBinaryType.Decimal,
            MaxBytes = 17, MaxDigits = 38, MaxDecimalDigits = 38, IsPrecisionScaleType = true)]
        Decimal,
        [SqlTypeInfo("numeric", typeof(decimal), SqlBinaryType.Decimal,
            MaxBytes = 17, MaxDigits = 38, MaxDecimalDigits = 38, IsPrecisionScaleType = true)]
        Numeric,
        #endregion

        #region Character
        [SqlTypeInfo("char", typeof(string), SqlBinaryType.Character, MaxBytes = 8000, IsVariableType = true)]
        Char,
        [SqlTypeInfo("varchar", typeof(string), SqlBinaryType.Character, MaxBytes = 8000, IsVariableType = true)]
        VarChar,
        [SqlTypeInfo("text", typeof(string), SqlBinaryType.Character)]
        Text,
        #endregion

        #region Unicode
        [SqlTypeInfo("nchar", typeof(string), SqlBinaryType.Unicode, MaxBytes = 8000, IsVariableType = true)]
        NChar,
        [SqlTypeInfo("nvarchar", typeof(string), SqlBinaryType.Unicode, MaxBytes = 8000, IsVariableType = true)]
        NVarChar,
        [SqlTypeInfo("ntext", typeof(string), SqlBinaryType.Unicode, MaxBytes = 8000, IsVariableType = true)]
        NText,
        #endregion

        #region Binary
        [SqlTypeInfo("binary", typeof(Binary), SqlBinaryType.Binary, MaxBytes = 8000)]
        Binary,
        [SqlTypeInfo("varbinary", typeof(Binary), SqlBinaryType.Binary, MaxBytes = 8000, IsVariableType = true)]
        VarBinary,
        [SqlTypeInfo("image", typeof(Binary), SqlBinaryType.Binary, MaxBytes = int.MaxValue, IsVariableType = true)]
        Image,
        [SqlTypeInfo("filestream", typeof(Binary), SqlBinaryType.Binary)]
        FileStream,
        #endregion

        #region DateTime
        [SqlTypeInfo("smalldatetime", typeof(DateTime), SqlBinaryType.DateTime, MaxBytes = 4, MaxDigits = 16)]
        SmallDateTime,
        [SqlTypeInfo("datetime", typeof(DateTime), SqlBinaryType.DateTime, MaxBytes = 8, MaxDigits = 23, MaxDecimalDigits = 3)]
        DateTime,
        [SqlTypeInfo("datetime2", typeof(DateTime), SqlBinaryType.DateTime)]
        DateTime2,
        [SqlTypeInfo("datetimeoffset", typeof(DateTime), SqlBinaryType.DateTime)]
        DateTimeOffset,
        [SqlTypeInfo("date", typeof(DateTime), SqlBinaryType.DateTime)]
        Date,
        [SqlTypeInfo("time", typeof(DateTime), SqlBinaryType.DateTime)]
        Time,
        [SqlTypeInfo("timestamp", typeof(DateTime), SqlBinaryType.DateTime, MaxBytes = 8, IsNullable = false)]
        TimeStamp,
        #endregion

        #region Other
        [SqlTypeInfo("hierarchyid", typeof(long), SqlBinaryType.Signed)]
        HierarchyId,
        [SqlTypeInfo("geometry", typeof(long), SqlBinaryType.Signed)]
        Geometry,
        [SqlTypeInfo("geography", typeof(long), SqlBinaryType.Signed)]
        Geography,

        [SqlTypeInfo("xml", typeof(XElement), SqlBinaryType.Unicode)]
        Xml,
        [SqlTypeInfo("sysname", typeof(string), SqlBinaryType.Character, MaxBytes = 256, IsNullable = false)]
        SysName,
        [SqlTypeInfo("uniqueidentifier", typeof(Guid), SqlBinaryType.Guid, MaxBytes = 16)]
        UniqueIdentifier,
        [SqlTypeInfo("sql_variant", typeof(object), SqlBinaryType.Object, MaxBytes = 8016)]
        SqlVariant,
        #endregion
    }
    public sealed class SqlType {

        #region Definitions
        private struct SqlUserType {

            #region Internal Members
            internal bool IsNillable { get; set; }
            internal int Precision { get; set; }
            internal int Scale { get; set; }
            internal int MaxLength { get; set; }
            #endregion
        }
        private sealed class SqlSystemType {

            #region Private Data Members
            private string m_name;
            private Type m_clrType;
            private SqlTypeType m_typeType;

            private bool m_hasPrecision;
            private bool m_hasScale;
            private bool m_hasLength;

            private Dictionary<SqlUserType, SqlType> m_instantiations;
            #endregion

            #region Constructor
            internal SqlSystemType(SqlTypeInfoAttribute sqlTypeInfo, SqlTypeType sqlTypeType) {
                m_name = sqlTypeInfo.Name;
                m_clrType = sqlTypeInfo.Type;
                m_hasPrecision = m_hasScale = sqlTypeInfo.IsPrecisionScaleType;
                m_hasLength = sqlTypeInfo.IsVariableType;
                m_typeType = sqlTypeType;
            }
            #endregion

            #region Internal Members
            internal string Name { get { return m_name; } }
            internal Type ClrType { get { return m_clrType; } }
            internal SqlTypeType Type { get { return m_typeType; } }

            internal bool HasPrecision { get { return m_hasPrecision; } }
            internal bool HasScale { get { return m_hasScale; } }
            internal bool HasLength { get { return m_hasLength; } }
            internal bool CanBeNillable { get { return true; } }

            internal SqlType MakeUserType(SqlUserType userType) {

                if (m_instantiations == null)
                    m_instantiations = new Dictionary<SqlUserType, SqlType>();

                var cached = default(SqlType);
                if (!m_instantiations.TryGetValue(userType, out cached))
                    m_instantiations[userType] = cached = new SqlType(this, userType);
                return cached;
            }
            #endregion

            #region Object Overrides
            public override string ToString() {
                return m_name;
            }
            #endregion
        }
        #endregion

        #region Public Internal Members
        internal static IEnumerable<SqlType> GetTypes() {
            return s_sqlTypes.Values.OrderBy(o => o.Name);
        }
        internal static SqlType GetType(string name) {
            return s_sqlTypes[name];
        }
        #endregion

        #region Static Constructor
        static SqlType() {
            try {
                s_sqlTypes =
                   (from o in typeof(SqlTypeType).Get<FieldInfo>(isStatic: true)
                    let attr = o.GetAttribute<SqlTypeInfoAttribute>()
                    where attr != null
                    orderby attr.Name
                    select new SqlType(new SqlSystemType(attr, (SqlTypeType)o.GetValue(null))))
                    .ToDictionary(o => o.Name);

            } catch (Exception e) {
                throw e;
            }
        }
        #endregion

        #region Static Private Data Members
        private static Dictionary<string, SqlType> s_sqlTypes;
        #endregion

        #region Private Data Members
        private SqlSystemType m_systemType;
        private SqlUserType? m_userType;
        #endregion

        #region Constructor
        private SqlType(SqlSystemType systemType)
            : this(systemType, null) {
        }
        private SqlType(SqlSystemType systemType, SqlUserType? userType) {
            m_systemType = systemType;
            m_userType = userType;
        }
        #endregion

        #region Privte Members
        private SqlUserType UserType {
            get {
                if (m_userType == null)
                    throw new InvalidOperationException();
                return m_userType.Value;
            }
        }
        #endregion

        #region Internal Members
        internal SqlType MakeUserType(bool isNillable, int precision, int scale, int maxLength) {
            if (!IsSystemType)
                throw new InvalidOperationException();
            var userType = new SqlUserType() {
                IsNillable = CanBeNillable ? isNillable : false,
                Precision = HasPrecision ? precision : 0,
                Scale = HasScale ? scale : 0,
                MaxLength = HasLength ? maxLength : 0,
            };
            return m_systemType.MakeUserType(userType);
        }
        #endregion

        #region Public Members
        public string Name { get { return m_systemType.Name; } }
        public Type ClrType { get { return m_systemType.ClrType; } }
        public bool IsUserDefinedType { get { return m_userType != null; } }
        public bool IsSystemType { get { return !IsUserDefinedType; } }
        public SqlTypeType SystemType { get { return m_systemType.Type; } }

        public bool HasPrecision { get { return m_systemType.HasPrecision; } }
        public bool HasScale { get { return m_systemType.HasScale; } }
        public bool HasLength { get { return m_systemType.HasLength; } }
        public bool CanBeNillable { get { return m_systemType.CanBeNillable; } }

        public bool IsNillable { get { return UserType.IsNillable; } }
        public int Precision { get { return UserType.Precision; } }
        public int Scale { get { return UserType.Scale; } }
        public int MaxLength { get { return UserType.MaxLength; } }
        #endregion
    }
    #endregion

    #region SqlCache                (SqlCache)
    internal class SqlCache<K, V> {

        #region Static Internal Members
        internal string GetComment(ISqlScope scope, Type type, K[] keys, bool include) {
            var comment = "{{{0}}} Get<{1}>({2})".Substitute(
                scope, type.Name,
                keys.Count() == 0 || !include ? default(string) :
                keys.Count() == 1 ? "key:" + keys.Single() :
                keys.Count().ToString());

            return comment;
        }
        #endregion

        #region Private Data Members
        private object m_parent;
        private SqlLoader m_loader;
        private List<Type> m_cachedTypes;
        private Dictionary<K, V> m_cache;

        private Func<Type, K[], bool, IEnumerable<V>> m_load;
        private Func<V, K> m_getKey;
        private Func<IEnumerable<V>, IEnumerable<V>> m_orderby;

        private Dictionary<Type, List<K>> m_whiteList;
        private Dictionary<Type, List<K>> m_blackList;
        #endregion

        #region Constructor
        internal SqlCache(
            Object parent,
            SqlLoader loader,
            Func<Type, K[], bool, IEnumerable<V>> load,
            Func<V, K> getKey,
            Func<IEnumerable<V>, IEnumerable<V>> orderby) {

            m_parent = parent;
            m_loader = loader;
            m_load = load;
            m_getKey = getKey;
            m_orderby = orderby;

            m_blackList = new Dictionary<Type, List<K>>();
            m_whiteList = new Dictionary<Type, List<K>>();

            m_cachedTypes = new List<Type>();
            if (typeof(K) == typeof(string))
                m_cache = new Dictionary<K, V>((IEqualityComparer<K>)StringComparer.InvariantCultureIgnoreCase);
            else
                m_cache = new Dictionary<K, V>();
        }
        #endregion

        #region Private Members
        private IEnumerable<K> GetList<T>(bool blackList) {
            var list = blackList ? m_blackList : m_whiteList;

            IEnumerable<K> result = Enumerable.Empty<K>();
            if (m_parent != null) {
                var dataSource = m_parent as SqlDataSource;
                var database = m_parent as SqlDatabase;
                var schema = m_parent as SqlSchema;

                if (dataSource != null)
                    result = (blackList ? dataSource.Loader.GetBlackList<T>() : dataSource.Loader.GetWhiteList<T>()).Cast<K>();
                if (database != null)
                    result = (blackList ? database.DataSource.GetBlackList<T>() : database.DataSource.GetWhiteList<T>()).Cast<K>();
                if (schema != null)
                    result = (blackList ? schema.Database.GetBlackList<T>() : schema.Database.GetWhiteList<T>()).Cast<K>();
            }

            if (list.ContainsKey(typeof(T)))
                result = result.Union(list[typeof(T)]);

            return result;
        }
        private void SetList<T>(bool blackList, IEnumerable<K> members) {
            //if (m_cache.Any(o => typeof(T).IsAssignableFrom(o)))
            //    throw new InvalidOperationException();
            var list = blackList ? m_blackList : m_whiteList;
            list[typeof(T)] = new List<K>(members);
        }

        private IEnumerable<V> LoadAndCache(Type type, IEnumerable<K> keys, bool include) {
            var loadedObjects = m_load(type, keys.ToArray(), include) ?? Enumerable.Empty<V>();
            loadedObjects = loadedObjects.ToArray();
            m_cache.AddRange(loadedObjects, o => m_getKey(o));
            return loadedObjects;
        }
        private bool AllValuesCached(Type type) {
            if (type == typeof(SqlDataSource))
                return false;
            return m_cachedTypes.Any(o => o.IsAssignableFrom(type));
        }
        #endregion

        #region Internal Members
        internal IEnumerable<T> GetLoaded<T>() {
            // blacklisted or non whitelist values could only have been loaded if they were requested explicitly
            return m_cache.Values.Cast<T>();
        }
        internal IEnumerable<T> OrderResult<T>(IEnumerable<T> result) {
            var distinctResult = result.Distinct();
            var orderedResult = m_orderby(distinctResult.Cast<V>());
            var resultOfT = orderedResult.WhereIs<T>();
            return resultOfT.ToArray();
        }
        internal virtual IEnumerable<T> Get<T>() {
            var whiteList = GetWhiteList<T>();
            var blackList = GetBlackList<T>();

            // cache all values
            if (!AllValuesCached(typeof(T))) {
                var keysToExclude = m_cache.Keys.Union(blackList);

                // if there is a white list then cache only white listed values not already cached
                if (whiteList.Any())
                    LoadAndCache(typeof(T), whiteList.Except(keysToExclude), true);
                else
                    LoadAndCache(typeof(T), keysToExclude, false);

                m_cachedTypes.Add(typeof(T));
            }

            // only return values that pass the white\black lists
            IEnumerable<V> resultOfV;
            if (whiteList.Any())
                resultOfV = m_cache.GetRange(whiteList.Except(blackList));
            else if (blackList.Any())
                resultOfV = m_cache.Values.Except(m_cache.GetRange(blackList));
            else
                resultOfV = m_cache.Values;

            // order the results
            var result = OrderResult(resultOfV.WhereIs<T>());
            return result;
        }
        internal virtual IEnumerable<T> Get<T>(params K[] keys) {

            // when the key is a string then grab everything in the scope
            if (typeof(K) == typeof(string) &&
                !m_loader.Options.EnableIncrementalLoading &&
                !AllValuesCached(typeof(T)))
                Get<T>();

            // get cached values
            IEnumerable<K> keysWithoutValues;
            var cached = m_cache.GetRange(keys, out keysWithoutValues);

            // load uncached values
            if (keysWithoutValues.Any()) {

                // if all are cached then only blacklisted ones remain to be loaded
                if (AllValuesCached(typeof(T))) {
                    if (GetWhiteList<T>().None())
                        keysWithoutValues = keysWithoutValues.Intersect(GetBlackList<T>()).ToArray();
                }

                if (keysWithoutValues.Any()) {
                    var loadedObjects = LoadAndCache(typeof(T), keysWithoutValues, true);
                    cached = cached.Concat(loadedObjects);

                    // if there is a cache miss then then cache everything so the next cache miss is fast
                    keysWithoutValues = keysWithoutValues.Except(loadedObjects.Select(o => m_getKey(o))).ToArray();
                    if (cached.Count() != keys.Count()) {
                        Console.WriteLine("Missed on {0}.Get<{1}>({2}) => load everything".Substitute(
                            m_parent, typeof(T).Name, keysWithoutValues.StringJoin(", ")));
                        Get<T>();
                    }
                }
            }

            var result = OrderResult(cached.WhereIs<T>());
            return result;
        }

        internal IEnumerable<K> GetBlackList<T>() {
            return GetList<T>(true);
        }
        internal IEnumerable<K> GetWhiteList<T>() {
            return GetList<T>(false);
        }
        internal void SetBlackList<T>(IEnumerable<K> members) {
            SetList<T>(true, members);
        }
        internal void SetWhiteList<T>(IEnumerable<K> members) {
            SetList<T>(false, members);
        }
        #endregion
    }
    internal class SqlScopeCache<V> : SqlCache<string, V> where V : ISqlScope {

        #region Constructor
        internal SqlScopeCache(
            object parent,
            SqlLoader loader,
            Func<Type, string[], bool, IEnumerable<V>> load,
            Func<V, string> getKey,
            Func<IEnumerable<V>, IEnumerable<V>> orderby)
            : base(parent, loader, load, getKey, orderby) {
        }
        #endregion

        #region Internal Members
        internal override IEnumerable<T> Get<T>(params string[] names) {
            if (typeof(T) == typeof(V))
                return base.Get<T>(names);

            return Get<V>().SelectMany(o => o.Get<T>(names)).Distinct();
        }
        internal override IEnumerable<T> Get<T>() {
            if (typeof(T) == typeof(V))
                return base.Get<T>();

            return Get<V>().SelectMany(o => o.Get<T>()).Distinct();
        }
        #endregion
    }
    #endregion

    #region ISqlScope               (ISqlScope, SqlConnectionString, SqlDataContext, SqlCustomObjectScope)
    public interface ISqlScope {
        IEnumerable<T> Get<T>();
        IEnumerable<T> Get<T>(params string[] names);
    }

    internal sealed partial class SqlDataContext : DataContext {

        #region Static Private Members
        private static long s_id = 0;
        #endregion

        #region Public Members
        private SqlConnectionString m_connectionString;
        #endregion

        #region Constructor
        internal SqlDataContext(SqlConnectionString connectionString, string comment)
            : this(new SqlConnectionString(connectionString) {
                ApplicationName = connectionString.ApplicationName + " " + s_id++ + ": " + comment
            }) {

            Console.WriteLine(connectionString.ApplicationName + " " + (s_id - 1) + ": " + comment);
        }
        private SqlDataContext(SqlConnectionString connectionString)
            : base(connectionString.ToString()) {
            m_connectionString = connectionString;
        }
        #endregion

        #region Object Overrides
        public override string ToString() {
            return m_connectionString.ToString();
        }
        #endregion
    }

    public sealed class SqlConnectionString {

        #region Constructor
        public SqlConnectionString(string dataSource, string database, string userId, string password) {
            DataSource = dataSource;
            Database = database;
            UserId = userId;
            Password = password;
        }
        public SqlConnectionString(SqlConnectionString connectionString) {
            DataSource = connectionString.DataSource;
            Database = connectionString.Database;
            UserId = connectionString.UserId;
            Password = connectionString.Password;
            ApplicationName = connectionString.ApplicationName;
            Other = connectionString.Other;
        }
        public SqlConnectionString(string connectionString) {
            var builder = new SqlConnectionStringBuilder(connectionString);
            DataSource = builder.DataSource;
            Database = builder.InitialCatalog;
            UserId = builder.UserID;
            Password = builder.Password;

            object value;
            builder.TryGetValue("application name", out value);
            ApplicationName = value == null ? null : value.ToString();

            Other =
               (from o in builder.Cast<KeyValuePair<string, object>>()
                let knownValues = new[] { "data source", "initial catalog", "user id", "password", "application name" }
                where !knownValues.Contains(o.Key.ToLower())
                select o).ToDictionary(o => o.Key, o => o.Value);
        }
        #endregion

        #region Public Members
        public string DataSource { get; set; }
        public string Database { get; set; }
        public string UserId { get; set; }
        public string Password { get; set; }
        public string ApplicationName { get; set; }
        public Dictionary<string, object> Other { get; set; }
        #endregion

        #region Object Overrides
        public override int GetHashCode() {
            return new SqlConnectionStringBuilder(((SqlConnectionString)this).ToString()).GetHashCode();
        }
        public override bool Equals(object obj) {
            if (obj is string)
                obj = new SqlConnectionString((string)obj);
            var rhs = new SqlConnectionStringBuilder(((SqlConnectionString)obj).ToString());
            var lhs = new SqlConnectionStringBuilder(((SqlConnectionString)this).ToString());

            return lhs.EquivalentTo(rhs);
        }
        public override string ToString() {
            var builder = new SqlConnectionStringBuilder();
            builder.DataSource = DataSource;
            builder.InitialCatalog = Database;
            builder.UserID = UserId;
            builder.Password = Password;

            if (ApplicationName != null)
                builder.ApplicationName = ApplicationName;

            if (Other != null) {
                foreach (var o in Other)
                    builder.Add(o.Key, o.Value);
            }

            return builder.ToString();
        }
        #endregion
    }

    public sealed class SqlCustomObjectScope : ISqlScope {

        #region Private Data Members
        private ISqlScope[] m_scopes;
        private ILookup<string, ISqlScope> m_scopesByName;
        #endregion

        #region Constructor
        public SqlCustomObjectScope(IEnumerable<ISqlScope> scopes)
            : this(scopes.ToArray()) {
        }
        public SqlCustomObjectScope(params ISqlScope[] scopes) {
            m_scopes = scopes;
            m_scopesByName = scopes.ToLookup(o => GetScopeName(o));
        }
        #endregion

        #region Private Members
        private string GetScopeName(ISqlScope scope) {
            if (scope is SqlLoader)
                return null;

            if (scope is SqlDataSource)
                return ((SqlDataSource)scope).Name;

            if (scope is SqlDatabase)
                return ((SqlDatabase)scope).Name;

            if (scope is SqlSchema)
                return ((SqlSchema)scope).Name;

            return null;
        }
        #endregion

        #region ISqlObjectScope Members
        public IEnumerable<T> Get<T>() {
            return m_scopes.WhereIs<T>().Union(m_scopes.SelectMany(o => o.Get<T>())).Distinct();
        }
        public IEnumerable<T> Get<T>(params string[] names) {
            return m_scopes.SelectMany(o => o.Get<T>(names))
                .Union(names.SelectMany(o => m_scopesByName[o]).WhereIs<T>())
                .Distinct();
        }
        #endregion
    }
    #endregion

    #region SqlLoader               (SqlLoader, SqlLoaderOptions, SqlDataSource, SqlDatabase, SqlSchema)
    public sealed class SqlLoaderOptions {

        #region Constructor
        public SqlLoaderOptions() {
        }
        #endregion

        #region Public Members
        public bool ExposeDataSourceScope { get; set; }
        public bool ExposeDatabaseScope { get; set; }
        public bool ExposeSchemaScope { get; set; }
        public bool EnableIncrementalLoading { get; set; }
        #endregion
    }

    public sealed class SqlLoader : ISqlScope {

        #region Public Static Members
        public static IEnumerable<SqlType> SystemTypes {
            get { return SqlType.GetTypes(); }
        }
        public static SqlType GetType(string name) {
            return SqlType.GetType(name);
        }
        public static SqlLoader Connect(string connectionString) {
            return Connect(connectionString, null);
        }
        public static SqlLoader Connect(string connectionString, SqlLoaderOptions loaderOptions) {
            var sqlConnectionString = new SqlConnectionString(connectionString);
            if (!string.IsNullOrEmpty(sqlConnectionString.Database))
                return SqlDatabase.Connect(connectionString, loaderOptions).Loader;

            if (!string.IsNullOrEmpty(sqlConnectionString.DataSource))
                return SqlDataSource.Connect(connectionString, loaderOptions).Loader;

            return new SqlLoader(connectionString, loaderOptions);
        }
        #endregion

        #region Private Data Members
        private SqlLoaderOptions m_loaderOptions;
        private SqlConnectionString m_connectionString;
        private SqlScopeCache<SqlDataSource> m_dataSourcesByName;
        #endregion

        #region Constructor
        internal SqlLoader(string connectionString)
            : this(connectionString, null) {
        }
        internal SqlLoader(string connectionString, SqlLoaderOptions loaderOptions) {
            m_connectionString = new SqlConnectionString(connectionString);
            m_loaderOptions = loaderOptions ?? new SqlLoaderOptions();
            m_dataSourcesByName = new SqlScopeCache<SqlDataSource>(this, this,
                delegate(Type type, string[] keys, bool include) {
                    // no universal set to consider
                    if (!include)
                        return null;

                    var result =
                        from o in keys
                        select new SqlDataSource(this,
                            new SqlConnectionString(m_connectionString) {
                                DataSource = o,
                                Database = "master"
                            });

                    return result;
                },
                o => o.Name,
                o => o.OrderBy(x => x.Name));

            // by default only return dbo 
            SetWhiteList<SqlSchema>(new[] { SqlSchema.DefaultSchemaName });

            // by default black list builtin SQL databases
            SetBlackList<SqlDatabase>(new[] {
                SqlDatabase.Master,
                SqlDatabase.Model,
                SqlDatabase.Msdb,
                SqlDatabase.TempDatabase
            });
        }
        #endregion

        #region Public Members
        public T Connect<T>(string connectionString) {
            var sqlConnectionString = new SqlConnectionString(connectionString);

            var dataSource = GetDataSource(sqlConnectionString.DataSource);
            if (typeof(T) == typeof(SqlDataSource)) {
                if (!dataSource.ConnectionString.Equals(sqlConnectionString))
                    throw new ArgumentException();
                return (T)(object)dataSource;
            }

            var database = dataSource.ConnectToDatabase(sqlConnectionString.Database);
            if (typeof(T) == typeof(SqlDatabase)) {
                if (!database.ConnectionString.Equals(sqlConnectionString))
                    throw new ArgumentException();
                return (T)(object)database;
            }

            throw new InvalidOperationException();
        }
        public SqlLoaderOptions Options { get { return m_loaderOptions; } }
        public string UserId { get { return m_connectionString.UserId; } }
        public string Password { get { return m_connectionString.Password; } }
        public IEnumerable<SqlDataSource> LoadedDataSources {
            get { return m_dataSourcesByName.GetLoaded<SqlDataSource>(); }
        }
        #endregion

        #region Public Members (DataSource)
        public IEnumerable<SqlDataSource> DataSources {
            get { return Get<SqlDataSource>(); }
        }
        public SqlDataSource GetDataSource(string name) {
            return Get<SqlDataSource>(name).SingleOrDefault();
        }

        public IEnumerable<string> GetBlackList<T>() {
            return m_dataSourcesByName.GetBlackList<T>();
        }
        public IEnumerable<string> GetWhiteList<T>() {
            return m_dataSourcesByName.GetWhiteList<T>();
        }
        public void SetWhiteList<T>(IEnumerable<string> members) {
            m_dataSourcesByName.SetWhiteList<T>(members);
        }
        public void SetBlackList<T>(IEnumerable<string> members) {
            m_dataSourcesByName.SetBlackList<T>(members);
        }
        #endregion

        #region Object Overrides
        public override string ToString() {
            return UserId;
        }
        #endregion

        #region ISqlScope Members
        public IEnumerable<T> Get<T>() {
            return m_dataSourcesByName.Get<T>();
        }
        public IEnumerable<T> Get<T>(params string[] names) {
            return m_dataSourcesByName.Get<T>(names);
        }
        #endregion
    }

    public sealed class SqlDataSource : SqlEntity, ISqlScope {

        #region Static Public Members
        public static SqlDataSource Connect(string connectionString) {
            return Connect(connectionString, null);
        }
        public static SqlDataSource Connect(string connectionString, SqlLoaderOptions loaderOptions) {
            var sqlConnectionString = new SqlConnectionString(connectionString);
            if (!string.IsNullOrEmpty(sqlConnectionString.Database))
                return SqlDatabase.Connect(connectionString, loaderOptions).DataSource;

            var loader = new SqlLoader(connectionString, loaderOptions);
            var dataSource = loader.GetDataSource(sqlConnectionString.DataSource);
            if (dataSource == null)
                throw new ArgumentException();

            return dataSource;
        }
        #endregion

        #region Private Data Members
        private SqlConnectionString m_sqlConnectionString;
        private string m_name;
        private SqlScopeCache<SqlDatabase> m_databasesByName;
        #endregion

        #region Constructor
        internal SqlDataSource(SqlLoader loader, SqlConnectionString connectionString)
            : base(loader) {
            m_sqlConnectionString = connectionString;
            m_name = m_sqlConnectionString.DataSource;
            m_databasesByName = new SqlScopeCache<SqlDatabase>(this, Loader,
                delegate(Type type, string[] keys, bool include) {
                    var comment = m_databasesByName.GetComment(this, type, keys, include);

                    var result =
                        from o in GetDataContext(comment).GetTable<Databases>()
                        where include ? keys.Contains(o.Name.ToLower()) : !keys.Contains(o.Name.ToLower())
                        select new SqlDatabase(this, o, m_sqlConnectionString);
                    return result;
                },
                o => o.Name,
                o => o.OrderBy(x => x.Name));
        }
        #endregion

        #region Internal Members
        internal SqlDataContext GetDataContext(string comment) {
            return new SqlDataContext(m_sqlConnectionString, comment);
        }
        internal IEnumerable<SqlDatabase> GetLoadedDatabases() {
            return m_databasesByName.GetLoaded<SqlDatabase>();
        }
        #endregion

        #region Public Members
        public string Name {
            get { return m_name; }
        }
        public SqlConnectionString ConnectionString {
            get { return m_sqlConnectionString; }
        }
        #endregion

        #region Public Members (Databases)
        public SqlDatabase ModelDatabase {
            get { return ConnectToDatabase("model"); }
        }
        public SqlDatabase MasterDatabase {
            get { return ConnectToDatabase("master"); }
        }
        public SqlDatabase ConnectToDatabase(string name) {
            return Get<SqlDatabase>(name).SingleOrDefault();
        }
        public IEnumerable<SqlDatabase> GetDatabases() {
            return Get<SqlDatabase>();
        }

        public IEnumerable<string> GetBlackList<T>() {
            return m_databasesByName.GetBlackList<T>();
        }
        public IEnumerable<string> GetWhiteList<T>() {
            return m_databasesByName.GetWhiteList<T>();
        }
        public void SetWhiteList<T>(IEnumerable<string> members) {
            m_databasesByName.SetWhiteList<T>(members);
        }
        public void SetBlackList<T>(IEnumerable<string> members) {
            m_databasesByName.SetBlackList<T>(members);
        }
        #endregion

        #region Object Overrides
        public override string ToString() {
            return SqlObjectName.TSqlEscape(Name);
        }
        #endregion

        #region ISqlScope Members
        public IEnumerable<T> Get<T>() {
            return m_databasesByName.Get<T>();
        }
        public IEnumerable<T> Get<T>(params string[] names) {
            return m_databasesByName.Get<T>(names);
        }
        #endregion
    }

    public sealed class SqlDatabase : SqlEntity, ISqlScope {

        #region Constants
        public const string Master = "Master";
        public const string Model = "Model";
        public const string Msdb = "Msdb";
        public const string TempDatabase = "TempDb";
        #endregion

        #region Static Public Members
        public static SqlDatabase Connect(string connectionString) {
            return Connect(connectionString, null);
        }
        public static SqlDatabase Connect(string connectionString, SqlLoaderOptions loaderOptions) {
            var sqlConnectionString = new SqlConnectionString(connectionString);
            var loader = new SqlLoader(connectionString, loaderOptions);

            var dataSource = loader.GetDataSource(sqlConnectionString.DataSource);
            if (dataSource == null)
                throw new ArgumentException();

            var database = dataSource.ConnectToDatabase(sqlConnectionString.Database);
            if (database == null)
                throw new ArgumentException();

            return database;
        }
        #endregion

        #region Private Data Members
        private string m_name;
        private SqlDataSource m_dataSource;
        private SqlConnectionString m_connectionString;

        // SqlTypes types can be resolved by Id
        private Dictionary<int, SqlType> m_systemTypesById;

        // SqlObjects can resolved by Id
        private SqlCache<int, SqlObject> m_objectsById;

        private SqlScopeCache<SqlSchema> m_schemasByName;
        private SqlCache<int, SqlSchema> m_schemasById;
        #endregion

        #region Constructor
        internal SqlDatabase(SqlDataSource dataSource, Databases database, SqlConnectionString connectionString)
            : base(dataSource.Loader) {
            m_dataSource = dataSource;
            m_name = database.Name;
            m_connectionString = new SqlConnectionString(connectionString) { Database = m_name };

            #region SchemasById & SchemasByName
            m_schemasById = new SqlCache<int, SqlSchema>(this, Loader,
                delegate(Type type, int[] ids, bool include) {
                    var comment = m_schemasById.GetComment(this, type, ids, include);

                    var result =
                        from o in GetDataContext(comment).GetTable<Schemas>()
                        where include ? ids.Contains(o.SchemaId) : !ids.Contains(o.SchemaId)
                        select new SqlSchema(this, o);
                    return result;
                },
                o => o.SchemaId,
                o => o.OrderBy(x => x.Name));

            m_schemasByName = new SqlScopeCache<SqlSchema>(this, Loader,
                delegate(Type type, string[] names, bool include) {
                    var comment = m_schemasByName.GetComment(this, type, names, include);

                    var ids =
                        from o in GetDataContext(comment).GetTable<Schemas>()
                        where include ? names.Contains(o.Name.ToLower()) : !names.Contains(o.Name.ToLower())
                        select o.SchemaId;
                    return m_schemasById.Get<SqlSchema>(ids.ToArray());
                },
                o => o.Name,
                o => o.OrderBy(x => x.Name));
            #endregion

            #region ObjectsById
            m_objectsById = new SqlCache<int, SqlObject>(this, Loader,
                delegate(Type sqlObjectType, int[] ids, bool include) {

                    #region Load ObjectIds
                    var comment = m_objectsById.GetComment(this, sqlObjectType, ids, include);
                    var dataContext = GetDataContext(comment);

                    var loadedObjects =
                       (from o in dataContext.GetTable<AllObjects>()
                        where include ? ids.Contains(o.ObjectId) : !ids.Contains(o.ObjectId)
                        let type = o.Type.Trim()
                        //where sqlObjectType.GetSqlTypes().Contains(type)
                        select
                            // SqlTable
                            type == SqlObjectType.UserTable ||
                            type == SqlObjectType.View ||
                            type == SqlObjectType.SystemTable ?
                                (SqlObject)new SqlTable(this, o,
                                    from c in dataContext.GetTable<AllColumns>()
                                    where c.ObjectId == o.ObjectId
                                    orderby c.ColumnId
                                    select new SqlColumn(this, c,
                                       (from x in dataContext.GetTable<IdentityColumns>()
                                        where x.ObjectId == o.ObjectId && x.ColumnId == c.ColumnId
                                        select x).SingleOrDefault()),

                                    from c in dataContext.GetTable<Indexes>()
                                    where c.ObjectId == o.ObjectId
                                    select c,

                                    from c in dataContext.GetTable<IndexColumns>()
                                    where c.ObjectId == o.ObjectId
                                    select c) :

                            // SqlSynonym
                            type == SqlObjectType.Synonym ?
                                (SqlObject)new SqlSynonym(this, o) :

                            // SqlForeignKey
                            type == SqlObjectType.ForeignKeyConstraint ?
                                (SqlObject)new SqlForeignKey(this, o,
                                   (from fkc in dataContext.GetTable<ForeignKeyColumns>()
                                    where fkc.ConstraintObjectId == o.ObjectId
                                    orderby fkc.ConstraintColumnId
                                    select SqlColumnPointer.ToKeyValuePair(this, fkc)).ToArray()) :

                            // SqlKeyConstraint
                            type == SqlObjectType.PrimaryKeyConstraint ||
                            type == SqlObjectType.UniqueConstraint ?
                                (SqlObject)new SqlKeyConstraint(this, o) :

                            (SqlObject)null).ToArray();
                    #endregion

                    if (loadedObjects.Any(o => o == null))
                        throw new InvalidOperationException();

                    #region Load Table Associated Objects
                    var tableIds = loadedObjects.WhereIs<SqlTable>().Select(o => o.ObjectId).ToArray();
                    if (tableIds.Any()) {
                        //var comment = m_objectsById.GetComment(this, typeof(SqlObject), ids, include);
                        //var dataContext = GetDataContext(comment);
                    }
                    #endregion

                    return loadedObjects;
                },
                o => o.ObjectId,
                o => o.OrderBy(x => x.ObjectName));
            #endregion
        }
        #endregion

        #region Internal Members
        internal SqlType GetSystemType(int systemTypeId) {
            if (m_systemTypesById == null) {
                m_systemTypesById =
                   (from o in GetDataContext("GetSystemTypesIds").GetTable<Types>().ToArray()
                    join t in SqlLoader.SystemTypes on o.Name.ToLower() equals t.Name.ToLower() into t
                    let sqlType = t.Single()
                    select new { Key = (int)o.UserTypeId, Value = sqlType })
                    .ToDictionary(o => o.Key, o => o.Value);
            }

            return m_systemTypesById[systemTypeId];
        }
        internal IEnumerable<SqlObject> GetObjects(params int[] objectIds) {
            return m_objectsById.Get<SqlObject>(objectIds);
        }
        internal DataContext GetDataContext(string comment) {
            return new SqlDataContext(m_connectionString, comment);
        }
        internal IEnumerable<SqlSchema> GetLoadedSchemas() {
            return m_schemasByName.GetLoaded<SqlSchema>();
        }
        #endregion

        #region Public Members
        public SqlConnectionString ConnectionString {
            get { return m_connectionString; }
        }
        public string Name {
            get { return m_name; }
        }
        public SqlDataSource DataSource {
            get { return m_dataSource; }
        }

        public IEnumerable<string> GetBlackList<T>() {
            return m_schemasByName.GetBlackList<T>();
        }
        public IEnumerable<string> GetWhiteList<T>() {
            return m_schemasByName.GetWhiteList<T>();
        }
        public void SetWhiteList<T>(IEnumerable<string> members) {
            m_schemasByName.SetWhiteList<T>(members);
        }
        public void SetBlackList<T>(IEnumerable<string> members) {
            m_schemasByName.SetBlackList<T>(members);
        }
        #endregion

        #region Public Members (Schema Resolution)
        public IEnumerable<SqlSchema> GetSchemas() {
            return Get<SqlSchema>();
        }
        public SqlSchema GetSchema(string name) {
            return Get<SqlSchema>(name).SingleOrDefault();
        }
        public SqlSchema GetSchema(int schemaId) {
            return m_schemasById.Get<SqlSchema>(schemaId).Single();
        }
        public SqlSchema DefaultSchema {
            get { return GetSchema(SqlSchema.DefaultSchemaName); }
        }
        public SqlSchema SystemSchema {
            get { return GetSchema(SqlSchema.SystemSchemaName); }
        }
        #endregion

        #region Public Members (Object Resolution By Id)
        public SqlObject GetObject(int objectId) {
            return GetObjects(objectId).SingleOrDefault();
        }
        public SqlSynonym GetSynonym(int synonymId) {
            return GetObject(synonymId) as SqlSynonym;
        }
        public SqlTable GetTable(int tableId) {
            return GetObject(tableId) as SqlTable;
        }
        public SqlForeignKey GetForeignKey(int foreignKeyId) {
            return GetObject(foreignKeyId) as SqlForeignKey;
        }
        public SqlKeyConstraint GetKeyConstraint(int keyConstraintId) {
            return GetObject(keyConstraintId) as SqlKeyConstraint;
        }
        #endregion

        #region Object Members
        public override string ToString() {
            return DataSource.ToString() + "." + SqlObjectName.TSqlEscape(Name);
        }
        #endregion

        #region ISqlObjectScope Members
        public IEnumerable<T> Get<T>() {
            return m_schemasByName.Get<T>();
        }
        public IEnumerable<T> Get<T>(params string[] names) {
            return m_schemasByName.Get<T>(names);
        }
        #endregion
    }

    public sealed class SqlSchema : SqlEntity, ISqlScope {

        #region Static Public Members
        public static string SystemSchemaName = "sys";
        public static string DefaultSchemaName = "dbo";
        #endregion

        #region Private Data Members
        private int m_schemaId;
        private SqlDatabase m_database;
        private string m_name;

        private SqlCache<string, SqlObject> m_objectsByName;
        #endregion

        #region Constructor
        internal SqlSchema(SqlDatabase database, Schemas schema)
            : base(database.Loader) {
            m_database = database;
            m_schemaId = schema.SchemaId;
            m_name = schema.Name;

            m_objectsByName = new SqlCache<string, SqlObject>(this, Loader,
                delegate(Type type, string[] names, bool include) {
                    var comment = m_objectsByName.GetComment(this, type, names, include);
                    var dataContext = m_database.GetDataContext(comment);
                    var ids =
                        from o in dataContext.GetTable<AllObjects>()
                        where type.GetSqlTypes().Contains(o.Type.Trim())
                        where o.SchemaId == m_schemaId
                        where include ? names.Contains(o.Name.ToLower()) : !names.Contains(o.Name.ToLower())
                        select o.ObjectId;
                    return m_database.GetObjects(ids.ToArray());
                },
                o => o.Name,
                o => o.OrderBy(x => x.Namespace));
        }
        #endregion

        #region Public Members
        public int SchemaId {
            get { return m_schemaId; }
        }
        public string Name {
            get { return m_name; }
        }
        public SqlDataSource DataSource {
            get { return Database.DataSource; }
        }
        public SqlDatabase Database {
            get { return m_database; }
        }

        public IEnumerable<string> GetBlackList<T>() {
            return m_objectsByName.GetBlackList<T>();
        }
        public IEnumerable<string> GetWhiteList<T>() {
            return m_objectsByName.GetWhiteList<T>();
        }
        public void SetWhiteList<T>(IEnumerable<string> members) {
            m_objectsByName.SetWhiteList<T>(members);
        }
        public void SetBlackList<T>(IEnumerable<string> members) {
            m_objectsByName.SetBlackList<T>(members);
        }
        #endregion

        #region Object Overrides
        public override string ToString() {
            return Database.ToString() + "." + SqlObjectName.TSqlEscape(Name);
        }
        #endregion

        #region ISqlScope Members
        public IEnumerable<T> Get<T>() {
            return m_objectsByName.Get<T>();
        }
        public IEnumerable<T> Get<T>(params string[] names) {
            var result = m_objectsByName.Get<T>(names);

            if (typeof(T) != typeof(SqlSynonym)) {
                var synonyms = m_objectsByName.Get<SqlSynonym>(names);
                var synonymTargets = synonyms.Select(o => o.TargetObject).WhereIs<T>();
                result = m_objectsByName.OrderResult<T>(result.Union(synonymTargets));
            }

            return result;
        }
        #endregion
    }
    #endregion

    #region SqlEntity               (SqlEntity, SqlDatabaseEntity, SqlTableEntity, SqlIndex, SqlColumn)
    public abstract class SqlEntity {

        #region Private Data Members
        private SqlLoader m_loader;
        #endregion

        #region Constructor
        internal SqlEntity(SqlLoader loader) {
            m_loader = loader;
        }
        #endregion

        #region Abstract Members
        public SqlLoader Loader { get { return m_loader; } }
        #endregion
    }

    public abstract class SqlDatabaseEntity : SqlEntity {

        #region Private Data Members
        private SqlDatabase m_database;
        private SqlObjectName m_namespace;
        #endregion

        #region Constructor
        internal SqlDatabaseEntity(SqlDatabase database)
            : base(database.Loader) {
            m_database = database;
        }
        internal void Initialize(SqlObjectName ns) {
            m_namespace = ns;
        }
        #endregion

        #region Public Members
        public SqlDataSource DataSource { get { return m_database.DataSource; } }
        public SqlDatabase Database { get { return m_database; } }
        public SqlObjectName Namespace { get { return m_namespace; } }
        #endregion

        #region Public Members (ToString)
        public string GetLoaderQualifiedToString(SqlNamespace scope) {
            return ToString(Namespace.GetLoaderQualifiedScope(Loader, scope));
        }
        public string GetRelativelyQualifiedToString(SqlObjectName relativeTo) {
            return GetLoaderQualifiedToString(Namespace.GetRelativelyQualifiedScope(relativeTo));
        }
        public string GetDataSourceQualifiedToString() {
            return GetLoaderQualifiedToString(SqlNamespace.DataSource);
        }
        public string GetDatabaseQualifiedToString() {
            return GetLoaderQualifiedToString(SqlNamespace.Database);
        }
        public string GetSchemaQualifiedToString() {
            return GetLoaderQualifiedToString(SqlNamespace.Schema);
        }
        public string GetUnqualifiedToString() {
            return GetLoaderQualifiedToString(SqlNamespace.Unqualified);
        }
        #endregion

        #region Public Abstract Members
        public abstract string ToString(SqlNamespace scope);
        #endregion
    }

    public abstract class SqlTableEntity : SqlDatabaseEntity {

        #region Private Data Member
        private SqlTable m_table;
        #endregion

        #region Constructor
        internal SqlTableEntity(SqlDatabase database)
            : base(database) {
        }
        internal void Initialize(SqlTable table) {
            m_table = table;
            Initialize(table.Namespace);
        }
        #endregion

        #region Public Members
        public SqlTable Table { get { return m_table; } }
        #endregion
    }

    public enum IndexType {
        Heap,
        Clustered,
        NonClustered,
    }
    public sealed class SqlIndex : SqlTableEntity {

        #region Private Data Members
        private int m_id;
        private IEnumerable<SqlColumn> m_columns;

        private string m_name;
        private IndexType m_indexType;
        private bool m_isUnique;
        private bool m_isPrimaryKey;
        #endregion

        #region Constructor
        internal SqlIndex(SqlDatabase database, Indexes index)
            : base(database) {
            m_id = index.IndexId;
            m_name = index.Name;
            m_indexType = (IndexType)index.Type;
            m_isUnique = index.IsUnique.Value;
            m_isPrimaryKey = index.IsPrimaryKey.Value;
        }
        internal void Initialize(SqlTable table, SqlColumn[] columns) {
            m_columns = columns;
            Initialize(table);
        }
        #endregion

        #region Public Members
        public int IndexId { get { return m_id; } }
        public IEnumerable<SqlColumn> GetColumns() { return m_columns; }
        public string Name { get { return m_name; } }
        public IndexType IndexType { get { return m_indexType; } }
        public bool IsUnique { get { return m_isUnique; } }
        public bool IsPrimaryKey { get { return m_isPrimaryKey; } }
        public bool IsSingleColumn { get { return GetColumns().Count() == 1; } }
        #endregion

        #region Object Overrides
        public override string ToString() {
            return this.GetLoaderQualifiedToString(SqlNamespace.DataSource);
        }
        #endregion

        #region SqlDatabaseEntity Overrides
        public sealed override string ToString(SqlNamespace scope) {
            return "{0}::{1}".Substitute(Table.ToString(scope), m_name);
        }
        #endregion
    }

    internal sealed class SqlColumnPointer {

        #region Static Internal Members
        internal static KeyValuePair<SqlColumnPointer, SqlColumnPointer>
            ToKeyValuePair(SqlDatabase database, ForeignKeyColumns fkc) {
            return new KeyValuePair<SqlColumnPointer, SqlColumnPointer>(
                new SqlColumnPointer(database, fkc.ParentObjectId, fkc.ParentColumnId),             // referencedFrom
                new SqlColumnPointer(database, fkc.ReferencedObjectId, fkc.ReferencedColumnId));    // referenceTo
        }
        #endregion

        #region Private Data Members
        private int m_columnId;
        private int m_tableId;
        private SqlDatabase m_database;
        private SqlTable m_table;
        private SqlColumn m_column;
        #endregion

        #region Constructor
        internal SqlColumnPointer(SqlDatabase database, int tableId, int columnId) {
            m_database = database;
            m_tableId = tableId;
            m_columnId = columnId;
        }
        #endregion

        #region Internal Members
        public int ColumnId { get { return m_columnId; } }
        public int TableId { get { return m_tableId; } }
        public SqlDatabase Database { get { return m_database; } }
        public SqlTable Table {
            get {
                if (m_table == null)
                    m_table = m_database.GetTable(m_tableId);
                return m_table;
            }
        }
        public SqlColumn Column {
            get {
                if (m_column == null)
                    m_column = Table.GetColumn(m_columnId);
                return m_column;
            }
        }
        #endregion

        #region Object Overrides
        public override string ToString() {
            return Column.ToString();
        }
        #endregion
    }

    public sealed class SqlColumn : SqlTableEntity {

        #region Private Data Members
        private int m_columnId;
        private string m_name;
        private SqlType m_sqlType;
        private IEnumerable<SqlIndex> m_indices;

        private bool m_isIdentityColumn;
        private long? m_seedValue;
        private long? m_incrementValue;
        #endregion

        #region Constructor
        internal SqlColumn(SqlDatabase database, AllColumns column, IdentityColumns identityColumn)
            : base(database) {
            m_columnId = column.ColumnId;
            m_sqlType =
                database.GetSystemType(column.SystemTypeId).MakeUserType(
                    column.IsNullable.Value,
                    column.Precision,
                    column.Scale,
                    column.MaxLength);
            m_name = column.Name;

            if (identityColumn != null) {
                m_isIdentityColumn = true;
                //m_seedValue = (long?)identityColumn.SeedValue;
                //m_incrementValue = (long?)identityColumn.IncrementValue;
            }
        }
        internal void Initialize(SqlTable table, SqlIndex[] indices) {
            m_indices = indices;
            Initialize(table);
        }
        #endregion

        #region Public Members
        public int ColumnId { get { return m_columnId; } }
        public SqlSchema Schema { get { return Table.Schema; } }
        public string Name { get { return m_name; } }
        public Type ClrType { get { return m_sqlType.ClrType; } }
        public SqlType SqlType { get { return m_sqlType; } }
        #endregion

        #region Public Members (IdentityColumn)
        public bool IsIdentityColumn { get { return m_isIdentityColumn; } }
        public long? SeedValue { get { return m_seedValue; } }
        public long? IncrementValue { get { return m_incrementValue; } }
        #endregion

        #region Public Members (Relationships)
        public IEnumerable<SqlIndex> GetIndices() { return m_indices; }
        public bool IsPrimaryKey { get { return m_indices.Any(x => x.IsPrimaryKey); } }

        public IEnumerable<SqlForeignKey> GetForeignKeys() {
            return GetReferencedFrom().Concat(GetReferenceTo());
        }
        public IEnumerable<SqlForeignKey> GetReferencedFrom() {
            var result =
                from o in Table.GetForeignKeys()
                where o.GetReferenceToColumns().Contains(this)
                select o;
            return result;
        }
        public IEnumerable<SqlForeignKey> GetReferenceTo() {
            var result =
                from o in Table.GetForeignKeys()
                where o.GetReferencedFromColumns().Contains(this)
                select o;
            return result;
        }

        public IEnumerable<SqlColumn> GetReferencedFromColumns() {
            var result =
                from o in GetForeignKeys()
                from x in o.GetColumns()
                where x.Value == this
                select x.Key;
            return result.Distinct();
        }
        public IEnumerable<SqlTable> GetReferencedFromTables() {
            return GetReferencedFromColumns().Select(o => o.Table).Distinct();
        }

        public IEnumerable<SqlColumn> GetReferenceToColumns() {
            var result =
                from o in GetForeignKeys()
                from x in o.GetColumns()
                where x.Key == this
                select x.Value;
            return result.Distinct();
        }
        public SqlColumn ReferenceToColumn() {
            return GetReferenceToColumns().SingleOrDefault();
        }
        public SqlTable ReferenceToTable() {
            return GetReferenceToColumns().Select(o => o.Table).SingleOrDefault();
        }
        #endregion

        #region Object Overrides
        public override string ToString() {
            return this.GetLoaderQualifiedToString(SqlNamespace.DataSource);
        }
        #endregion

        #region SqlDatabaseEntity Overrides
        public sealed override string ToString(SqlNamespace scope) {
            return "{0}::{1}".Substitute(Table.ToString(scope), m_name);
        }
        #endregion
    }
    #endregion

    #region SqlObject               (SqlObject, SqlNamespace, SqlObjectName, SqlObjectAttribute)
    [Flags]
    public enum SqlNamespace {
        Unqualified = 0,

        DataSourcePart = 1 << 0,
        DatabasePart = 1 << 1,
        SchemaPart = 1 << 2,

        DataSource = DataSourcePart | DatabasePart | SchemaPart,
        Database = DataSourcePart | DatabasePart,
        Schema = SchemaPart,
    }

    public sealed class SqlObjectName : IComparable {

        #region Static Explicit Operator
        public static explicit operator string(SqlObjectName sqlName) {
            return sqlName.ToString();
        }
        public static explicit operator SqlObjectName(string sqlName) {
            return new SqlObjectName(sqlName);
        }
        #endregion

        #region Static Internal Members
        internal static string TSqlEscape(string name) {
            if (name == null)
                return null;

            if (Regex.IsMatch(name, ".*[-.]"))
                return "[" + name + "]";
            return name;
        }
        #endregion

        #region Private Data Members
        private string m_dataSourceName;
        private string m_databaseName;
        private string m_schemaName;
        private string m_name;
        #endregion

        #region Constructor
        private SqlObjectName() { }
        public SqlObjectName(string dataSource, string database, string schema, string name) {
            Initialize(dataSource, database, schema, name);
        }
        public SqlObjectName(string qualifiedName)
            : this(qualifiedName, new SqlObjectName()) {
        }
        public SqlObjectName(string qualifiedName, SqlObjectName context) {
            var split = qualifiedName.Split(new[] { '.' }, StringSplitOptions.RemoveEmptyEntries).Reverse().ToArray();
            Initialize(
                split.ElementAtOrDefault(3) ?? context.DataSourceName,
                split.ElementAtOrDefault(2) ?? context.DatabaseName,
                split.ElementAtOrDefault(1) ?? context.SchemaName,
                split.ElementAtOrDefault(0));
        }
        public void Initialize(string dataSource, string database, string schema, string name) {
            if (dataSource == null) throw new ArgumentNullException();
            if (database == null) throw new ArgumentNullException();
            if (schema == null) throw new ArgumentNullException();
            if (name == null) throw new ArgumentNullException();

            m_dataSourceName = dataSource.Trim('[', ']');
            m_databaseName = database.Trim('[', ']');
            m_schemaName = schema.Trim('[', ']');
            m_name = name.Trim('[', ']');
        }
        #endregion

        #region Public Members
        public string DataSourceName {
            get { return m_dataSourceName; }
        }
        public string DatabaseName {
            get { return m_databaseName; }
        }
        public string SchemaName {
            get { return m_schemaName; }
        }
        public string Name {
            get { return m_name; }
        }

        public string DataSourceQualifiedName {
            get { return ToString(SqlNamespace.DataSource); }
        }
        public string DatabaseQualifiedName {
            get { return ToString(SqlNamespace.Database); }
        }
        public string SchemaQualifiedName {
            get { return ToString(SqlNamespace.Schema); }
        }
        public string UnqualifiedName {
            get { return ToString(SqlNamespace.Unqualified); }
        }

        public SqlNamespace GetRelativelyQualifiedScope(SqlObjectName relativeTo) {
            var scope = SqlNamespace.Unqualified;
            var lhs = this;
            var rhs = relativeTo;

            if (lhs.DataSourceName != rhs.DataSourceName)
                scope |= SqlNamespace.DataSourcePart;
            else if (lhs.DatabaseName != rhs.DatabaseName)
                scope |= SqlNamespace.DatabasePart;

            if (lhs.SchemaName != rhs.SchemaName)
                scope |= SqlNamespace.SchemaPart;

            return scope;
        }
        public string GetRelativelyQualifiedName(SqlObjectName relativeTo) {
            return ToString(GetRelativelyQualifiedScope(relativeTo));
        }

        public SqlNamespace GetLoaderQualifiedScope(SqlLoader loader, SqlNamespace scope) {
            var options = loader.Options;

            if (!options.ExposeDataSourceScope &&
                !loader.LoadedDataSources.MoreThan(1))
                scope &= ~SqlNamespace.DataSourcePart;

            if (!options.ExposeDatabaseScope &&
                !loader.LoadedDataSources
                    .SelectMany(o => o.GetLoadedDatabases()).MoreThan(1))
                scope &= ~SqlNamespace.DatabasePart;

            if (!options.ExposeSchemaScope) {
                var loadedSchemas = loader.LoadedDataSources
                    .SelectMany(o => o.GetLoadedDatabases())
                        .SelectMany(o => o.GetLoadedSchemas());

                var firstName = loadedSchemas.Select(o => o.Name.ToLower()).FirstOrDefault();
                if (firstName != null) {
                    if (!loadedSchemas.Skip(1)
                        .Select(o => o.Name.ToLower())
                            .Any(o => o != firstName))
                        scope &= ~SqlNamespace.SchemaPart;
                }
            }

            return scope;
        }
        public string GetLoaderQualifiedToString(SqlLoader loader, SqlNamespace scope) {
            return ToString(GetLoaderQualifiedScope(loader, scope));
        }

        public bool IsDefaultSchema {
            get { return SchemaName == SqlSchema.DefaultSchemaName; }
        }
        public bool IsSystemSchema {
            get { return SchemaName == SqlSchema.SystemSchemaName; }
        }
        public string ToString(SqlNamespace scope) {
            var result = new[] {
                scope.IsSet(SqlNamespace.DataSourcePart) ? TSqlEscape(DataSourceName) : null,
                scope.IsSet(SqlNamespace.DatabasePart)  ? TSqlEscape(DatabaseName) : null,
                scope.IsSet(SqlNamespace.SchemaPart)  ? TSqlEscape(SchemaName) : null,
                TSqlEscape(Name)
            }.Where(o => o != null).StringJoin(".");
            return result;
        }
        #endregion

        #region Object Overrides
        public override string ToString() {
            return DataSourceQualifiedName;
        }
        #endregion

        #region IComparable Members
        public int CompareTo(object obj) {
            var lhs = (SqlObjectName)obj;

            var result = DataSourceName.CompareTo(lhs.DataSourceName);
            if (result != 0) return result;

            result = DatabaseName.CompareTo(lhs.DatabaseName);
            if (result != 0) return result;

            result = SchemaName.CompareTo(lhs.SchemaName);
            if (result != 0) return result;

            result = Name.CompareTo(lhs.Name);
            return result;
        }
        #endregion
    }

    [AttributeUsage(AttributeTargets.Class, AllowMultiple = true)]
    internal class SqlObjectAttribute : Attribute {
        private object m_types;
        internal SqlObjectAttribute(object types) {
            m_types = types;
        }
        internal string[] Types {
            get {
                if (m_types is string) {
                    return new[] { (string)m_types };
                } else {
                    return new List<string>((IEnumerable<string>)m_types).ToArray();
                }
            }
        }
    }

    internal static class SqlObjectType {
        internal const string AggregateFunction = "AF";
        internal const string CheckConstraint = "C";
        internal const string DefaultConstraint = "D";
        internal const string ForeignKeyConstraint = "F";
        internal const string SqlScalarFunction = "FN";
        internal const string ClrScalarFunction = "FS";
        internal const string ClrTableFunction = "FT";
        internal const string SqlInlineTableFunction = "IF";
        internal const string InternalTable = "IT";
        internal const string SqlStoredProcedure = "P";
        internal const string ClrStoredProcedure = "PC";
        internal const string PlanGuide = "PG";
        internal const string PrimaryKeyConstraint = "PK";
        internal const string Rule = "R";
        internal const string ReplicationFilterProcedure = "RF";
        internal const string SystemTable = "S";
        internal const string Synonym = "SN";
        internal const string ServiceQueue = "SQ";
        internal const string ClrDmlTrigger = "TA";
        internal const string SqlTableFunction = "TF";
        internal const string SqlDmlTrigger = "TR";
        internal const string UserTable = "U";
        internal const string UniqueConstraint = "UQ";
        internal const string View = "V";
        internal const string ExtendedStoredProcedure = "X";
    }

    public abstract class SqlObject : SqlDatabaseEntity {

        #region Private Data Members
        private int m_id;
        private string m_type;
        private string m_name;
        private int m_schemaId;
        private SqlSchema m_schema;
        #endregion

        #region Constructor
        internal SqlObject(SqlDatabase database, AllObjects obj)
            : base(database) {

            m_id = obj.ObjectId;
            m_type = obj.Type.Trim();
            m_name = obj.Name;
            m_schemaId = m_schemaId = obj.SchemaId;
            Initialize(
                new SqlObjectName(
                    DataSource.Name,
                    Database.Name,
                    Schema.Name,
                    Name));
        }
        #endregion

        #region Public Members
        public int ObjectId {
            get { return m_id; }
        }
        public string Name {
            get { return m_name; }
        }
        public SqlObjectName ObjectName {
            get { return Namespace; }
        }
        public SqlSchema Schema {
            get {
                if (m_schema == null)
                    m_schema = Database.GetSchema(m_schemaId);
                return m_schema;
            }
        }
        #endregion

        #region Object Overrides
        public override string ToString() {
            return this.GetDataSourceQualifiedToString();
        }
        #endregion

        #region SqlDatabaseEntity Overrides
        public sealed override string ToString(SqlNamespace scope) {
            return ObjectName.ToString(scope);
        }
        #endregion
    }
    #endregion

    #region SqlObjects              (SqlTable, SqlSynonym, SqlKeyConstraint, SqlForeignKey)
    [SqlObject(new[] { SqlObjectType.UserTable, SqlObjectType.View, SqlObjectType.SystemTable })]
    public sealed class SqlTable : SqlObject {

        #region Private Data Members
        private bool m_isSystemTable;
        private bool m_isView;

        // SqlColumns
        private Dictionary<string, SqlColumn> m_columnsByName;
        private Dictionary<int, SqlColumn> m_columnsById;

        // SqlIndices
        private Dictionary<string, SqlIndex> m_indicesByName;
        private Dictionary<int, SqlIndex> m_indicesById;

        private IEnumerable<SqlKeyConstraint> m_keyConstraints;
        private IEnumerable<SqlForeignKey> m_foreignKeys;
        #endregion

        #region Constructor
        internal SqlTable(
            SqlDatabase database,
            AllObjects obj,
            IEnumerable<SqlColumn> columns,
            IEnumerable<Indexes> indexes,
            IEnumerable<IndexColumns> indexColumns)
            : base(database, obj) {

            columns = columns.ToArray();
            indexes = indexes.ToArray();
            indexColumns = indexColumns.ToArray();

            m_isView = obj.AsViews != null;
            if (!m_isView)
                m_isSystemTable = obj.Type.Trim() == SqlObjectType.SystemTable;

            // create indices
            var indices =
               (from o in indexes
                where o.Type != (int)IndexType.Heap
                join ic in indexColumns on o.IndexId equals ic.IndexId into ic
                let columnIds = ic.Select(x => x.ColumnId)
                select new {
                    SqlIndex = new SqlIndex(Database, o),
                    Columns = columnIds.ToHashSet()
                }).ToArray();

            // join indices with columns
            foreach (var o in indices)
                o.SqlIndex.Initialize(this,
                    (from x in o.Columns
                     join c in columns on x equals c.ColumnId
                     select c).ToArray());
            var sqlIndices = indices.Select(o => o.SqlIndex);
            m_indicesByName = sqlIndices.ToDictionary(o => o.Name.ToLower());
            m_indicesById = sqlIndices.ToDictionary(o => o.IndexId);

            // join columns with indices
            foreach (var o in columns)
                o.Initialize(this, sqlIndices.Where(i => i.GetColumns().Contains(o)).ToArray());

            m_columnsById = columns.ToDictionary(o => o.ColumnId);
            m_columnsByName = columns.ToDictionary(o => o.Name.ToLower());
        }
        #endregion

        #region Public Members
        public bool IsView { get { return m_isView; } }
        public bool IsSystemTable { get { return m_isSystemTable; } }
        #endregion

        #region Public Members (SqlColumn, SqlIndex)
        public IEnumerable<SqlColumn> GetColumns() {
            return m_columnsByName.Values.OrderBy(o => o.ColumnId);
        }
        public SqlColumn GetColumn(string name) {
            SqlColumn cached;
            m_columnsByName.TryGetValue(name.ToLower(), out cached);
            return cached;
        }
        public SqlColumn GetColumn(int columnId) {
            SqlColumn cached;
            m_columnsById.TryGetValue(columnId, out cached);
            return cached;
        }

        public IEnumerable<SqlIndex> GetIndices() {
            return m_indicesByName.Values.OrderBy(o => o.Name);
        }
        public SqlIndex GetIndex(string name) {
            SqlIndex cached;
            m_indicesByName.TryGetValue(name.ToLower(), out cached);
            return cached;
        }
        public SqlIndex GetIndex(int indexId) {
            SqlIndex cached;
            m_indicesById.TryGetValue(indexId, out cached);
            return cached;
        }
        #endregion

        #region Public Members (KeyConstraints, SqlForeignKey)
        public IEnumerable<SqlKeyConstraint> GetKeyConstraints() {
            if (m_keyConstraints == null) {
                m_keyConstraints = Database.GetObjects(
                   (from o in Database.GetDataContext("Table.KeyConstraints").GetTable<KeyConstraints>()
                    where o.ParentObjectId == ObjectId
                    select o.ObjectId).ToArray()).Cast<SqlKeyConstraint>();
            }

            return m_keyConstraints;
        }
        public SqlKeyConstraint GetKeyConstraint(string name) {
            return Schema.GetObject(name) as SqlKeyConstraint;
        }

        public IEnumerable<SqlForeignKey> GetForeignKeys() {
            if (m_foreignKeys == null) {
                m_foreignKeys = Database.GetObjects(
                   (from o in Database.GetDataContext("Table.Relationships").GetTable<ForeignKeys>()
                    where o.ParentObjectId == ObjectId || o.ReferencedObjectId == ObjectId
                    select o.ObjectId).ToArray()).Cast<SqlForeignKey>();
            }

            return m_foreignKeys;
        }
        public SqlForeignKey GetForeignKey(string name) {
            return Schema.GetObject(name) as SqlForeignKey;
        }
        public IEnumerable<SqlForeignKey> GetReferencedFrom() {
            var result =
            from o in GetForeignKeys()
            where o.ReferenceToTable == this
            select o;
            return result;
        }
        public IEnumerable<SqlForeignKey> GetReferencesTo() {
            var result =
                from o in GetForeignKeys()
                where o.ReferencedFromTable == this
                select o;
            return result;
        }
        public IEnumerable<SqlTable> GetReferencedFromTables() {
            return GetReferencedFrom().Select(o => o.ReferencedFromTable).Distinct();
        }
        public IEnumerable<SqlTable> GetReferencesToTables() {
            return GetReferencesTo().Select(o => o.ReferenceToTable).Distinct();
        }
        #endregion

        #region Object Overrides
        public override string ToString() {
            if (!IsView)
                return base.ToString();

            return "{0} (view)".Substitute(base.ToString());
        }
        #endregion
    }

    [SqlObject(SqlObjectType.Synonym)]
    public sealed class SqlSynonym : SqlObject {

        #region Private Data Members
        private SqlObjectName m_sqlObjectName;
        private SqlDataSource m_targetDataSource;
        private SqlDatabase m_targetDatabase;
        private SqlSchema m_targetSchema;
        private SqlObject m_targetSqlObject;
        #endregion

        #region Constructor
        internal SqlSynonym(SqlDatabase database, AllObjects obj)
            : base(database, obj) {
            var synonym = obj.AsSynonyms;
            m_sqlObjectName = new SqlObjectName(synonym.BaseObjectName, Namespace);
        }
        #endregion

        #region Public Members
        public SqlDataSource TargetDataSource {
            get {
                if (m_targetDataSource == null)
                    m_targetDataSource = Database.Loader.GetDataSource(m_sqlObjectName.DataSourceName);
                return m_targetDataSource;
            }
        }
        public SqlDatabase TargetDatabase {
            get {
                if (m_targetDatabase == null)
                    m_targetDatabase = TargetDataSource.ConnectToDatabase(m_sqlObjectName.DatabaseName);
                return m_targetDatabase;
            }
        }
        public SqlSchema TargetSchema {
            get {
                if (m_targetSchema == null)
                    m_targetSchema = TargetDatabase.GetSchema(m_sqlObjectName.SchemaName);
                return m_targetSchema;
            }
        }
        public SqlObject TargetObject {
            get {
                if (m_targetSqlObject == null)
                    m_targetSqlObject = TargetSchema.GetObject(m_sqlObjectName.Name);
                return m_targetSqlObject;
            }
        }
        public SqlTable TargetTable {
            get { return (SqlTable)TargetObject; }
        }
        #endregion

        #region Object Overrides
        public override string ToString() {
            return "{0} => {1}".Substitute(base.ToString(),
                m_sqlObjectName.ToString(
                    m_sqlObjectName.GetRelativelyQualifiedScope(Namespace)));
        }
        #endregion
    }

    [SqlObject(new[] { SqlObjectType.PrimaryKeyConstraint, SqlObjectType.UniqueConstraint })]
    public sealed class SqlKeyConstraint : SqlObject {

        #region Private Data Members
        private int m_tableId;
        private int m_indexId;
        private SqlTable m_table;
        private SqlIndex m_index;
        private bool m_isPrimaryKeyConstraint;
        private bool m_isUniqueConstraint;
        #endregion

        #region Constructor
        internal SqlKeyConstraint(SqlDatabase database, AllObjects obj)
            : base(database, obj) {

            var keyConstraint = obj.AsKeyConstraints;

            m_tableId = keyConstraint.ParentObjectId;
            m_indexId = (int)keyConstraint.UniqueIndexId;
            m_isPrimaryKeyConstraint = keyConstraint.Type == SqlObjectType.PrimaryKeyConstraint;
            m_isUniqueConstraint = keyConstraint.Type == SqlObjectType.UniqueConstraint;
        }
        #endregion

        #region Public Members
        public SqlTable Table {
            get {
                if (m_table == null)
                    m_table = Database.GetTable(m_tableId);
                return m_table;
            }
        }
        public SqlIndex Index {
            get {
                if (m_index == null)
                    m_index = Table.GetIndex(m_indexId);
                return m_index;
            }
        }
        public SqlColumn Column {
            get { return Index.GetColumns().Single(); }
        }
        public IEnumerable<SqlColumn> GetColumns() {
            return Index.GetColumns();
        }
        public bool IsSingleColumn { get { return Index.IsSingleColumn; } }
        public bool IsPrimaryKeyConstraint { get { return m_isPrimaryKeyConstraint; } }
        public bool IsUniqueConstraint { get { return m_isUniqueConstraint; } }
        #endregion

        #region Object Overrides
        public override string ToString() {
            if (!IsSingleColumn)
                return "{0} ({1} columns)".Substitute(base.ToString(), GetColumns().Count());

            return "{0} {{{1}{2}}} "
                .Substitute(base.ToString(), Column.GetRelativelyQualifiedToString(ObjectName),
                IsUniqueConstraint ? " (unique)" : null);
        }
        #endregion
    }

    public enum SqlReferentialAction {
        NoAction,
        Cascade,
        SetNull,
        SetDefault
    }
    [SqlObject(new[] { SqlObjectType.ForeignKeyConstraint })]
    public sealed class SqlForeignKey : SqlObject {

        #region Private Data Members
        private bool m_isDisabled;
        private bool m_isTrusted;
        private bool m_isForReplication;
        private SqlReferentialAction m_deleteReferentialAction;
        private SqlReferentialAction m_updateReferentialAction;
        private IEnumerable<KeyValuePair<SqlColumnPointer, SqlColumnPointer>> m_columnPointers;
        private IEnumerable<KeyValuePair<SqlColumn, SqlColumn>> m_columns;
        #endregion

        #region Constructor
        internal SqlForeignKey(SqlDatabase database, AllObjects obj,
            IEnumerable<KeyValuePair<SqlColumnPointer, SqlColumnPointer>> columns)
            : base(database, obj) {

            var foreignKey = obj.AsForeignKeys;

            m_isDisabled = foreignKey.IsDisabled;
            m_isTrusted = !foreignKey.IsNotTrusted;
            m_isForReplication = !foreignKey.IsNotForReplication;
            m_deleteReferentialAction = (SqlReferentialAction)foreignKey.DeleteReferentialAction;
            m_updateReferentialAction = (SqlReferentialAction)foreignKey.UpdateReferentialAction;
            m_columnPointers = columns;
        }
        #endregion

        #region Public Members
        public bool IsSingleColumn { get { return GetColumns().Count() == 1; } }
        public bool IsDisabled { get { return m_isDisabled; } }
        public bool IsTrusted { get { return m_isTrusted; } }
        public bool IsForReplication { get { return m_isForReplication; } }
        public SqlReferentialAction DeleteReferentialAction { get { return m_deleteReferentialAction; } }
        public SqlReferentialAction UpdateReferentialAction { get { return m_updateReferentialAction; } }
        public IEnumerable<KeyValuePair<SqlColumn, SqlColumn>> GetColumns() {
            if (m_columns == null) {
                m_columns =
                   (from o in m_columnPointers
                    select new KeyValuePair<SqlColumn, SqlColumn>(
                        o.Key.Column,
                        o.Value.Column))
                    .ToArray();
                m_columnPointers = null;
            }

            return m_columns;
        }
        public IEnumerable<SqlColumn> GetReferencedFromColumns() {
            return GetColumns().Select(o => o.Key);
        }
        public IEnumerable<SqlColumn> GetReferenceToColumns() {
            return GetColumns().Select(o => o.Value);
        }
        public SqlTable ReferencedFromTable {
            get { return GetReferencedFromColumns().First().Table; }
        }
        public SqlTable ReferenceToTable {
            get { return GetReferenceToColumns().First().Table; }
        }
        public SqlColumn ReferencedFromColumn {
            get { return GetReferencedFromColumns().SingleOrDefault(); }
        }
        public SqlColumn ReferenceToColumn {
            get { return GetReferenceToColumns().SingleOrDefault(); }
        }
        #endregion

        #region Object Overrides
        public override string ToString() {
            if (!IsSingleColumn)
                return "{0} ({1} columns)".Substitute(base.ToString(), GetColumns().Count());

            return "{0} {{{1} => {2}}} ".Substitute(
                base.ToString(),
                ReferencedFromColumn.GetUnqualifiedToString(),
                ReferenceToColumn.GetUnqualifiedToString());
        }
        #endregion
    }
    #endregion
}
